package com.ingenio.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.ingenio.Adapter;
import com.ingenio.business.Categoria;
import com.ingenio.business.Rubro;
import com.ingenio.business.SubRubro;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class RubroAdapter extends Adapter{

	public void agregarRubro(Rubro rubro) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		SubRubroAdapter sra = null;
		try {
	    	conn = (Connection) abrirConexion();
	    	sra = new SubRubroAdapter();
			String add ="INSERT into rubro (descRubro, IdCategoria, rubHabilitado) " +
					"VALUES (?, ?, 1)";
			stmt = (PreparedStatement) conn.prepareStatement(add, Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, rubro.getDescRubro());
			stmt.setInt(2, rubro.getCategoria().getIdCategoria());
			stmt.execute();
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
			    Integer id = rs.getInt(1);
			    rubro.setIdRubro(id);
			    sra.agregarSubRubro(rubro, conn);
			}
					
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		//conn.close();
    		cerrarConexion(conn);
    	}
	}
	
	public void eliminarRubro(Rubro rubro) throws Exception{
    	Connection conn = null;
    	PreparedStatement stmt = null;
    	SubRubroAdapter sra = new SubRubroAdapter();
    	try {
	    	conn = (Connection) abrirConexion();
	    	String del ="UPDATE rubro set rubHabilitado = 0 WHERE IdRubro = ?";
			stmt = (PreparedStatement) conn.prepareStatement(del);
			stmt.setInt(1, rubro.getIdRubro());
			stmt.execute();
			sra.eliminarSubRubros(rubro.getSubrubros(), conn);
		}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		//conn.close();
    		cerrarConexion(conn);
    	}
    }
	
	public void modificarRubro(Rubro rubro) throws Exception{
    	Connection conn = null;
    	PreparedStatement stmt = null;
    	try {
	    	conn = (Connection) abrirConexion();
	    	SubRubroAdapter sra = new SubRubroAdapter();
	    	String upd ="UPDATE rubro SET descRubro = ?, IdCategoria = ? WHERE IdRubro = ?";
			stmt = (PreparedStatement) conn.prepareStatement(upd);
	    	stmt.setString(1, rubro.getDescRubro());
	    	stmt.setInt(2, rubro.getCategoria().getIdCategoria());
	    	stmt.setInt(3, rubro.getIdRubro());
			stmt.executeUpdate();
			sra.modificarSubRubro(rubro, conn);
		}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		//conn.close();
    		cerrarConexion(conn);
    	}
    }
	
	public Rubro devolverRubro(Integer id) throws Exception{
    	Connection conn = null;
    	Rubro r = null;
    	PreparedStatement stmt = null;
    	
    	try {
    		conn = (Connection) abrirConexion();
    		String sel = "SELECT descRubro as descripcion, IdCategoria FROM rubro WHERE IdRubro =  ? and rubHabilitado = 1";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		stmt.setInt(1, id);
    		ResultSet result = stmt.executeQuery();
    		CategoriaAdapter ca = new CategoriaAdapter();
    		SubRubroAdapter sra = new SubRubroAdapter();
    		Categoria c = null;
    		if(result.next()) {
    			String descripcion = result.getString("descripcion");
    			Integer idCategoria = result.getInt("IdCategoria");
    			c = ca.devolverCategoria(idCategoria);
    			r = new Rubro(id, descripcion, c, true);
    			List<SubRubro> subrubros = sra.devolverSubRubros(r, conn);
    			r.setSubrubros(subrubros);
    		}
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		//conn.close();
    		cerrarConexion(conn);
    	}
		return r;
    }
	
	public List<Rubro> devolverTodos() throws Exception{
    	Connection conn = null;
    	List<Rubro> rubros = new ArrayList<Rubro>();
    	PreparedStatement stmt = null;
		try {
			conn = (Connection) abrirConexion();
			CategoriaAdapter ca = new CategoriaAdapter();
			Categoria c = null;
    		String sel ="SELECT idRubro , descRubro as descripcion, idCategoria from rubro WHERE rubHabilitado = 1";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		ResultSet result = stmt.executeQuery();
    		Rubro r = null;
    		while(result.next()){
    			Integer id = result.getInt("idRubro");
    			String descripcion = result.getString("descripcion");
    			Integer idCategoria = result.getInt("idCategoria");
    			c = ca.devolverCategoria(idCategoria);
    			r = new Rubro(id, descripcion, c, true);
    			rubros.add(r);
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		//conn.close();
    		cerrarConexion(conn);
    	}
    	return rubros;
    }  
	
	public List<Rubro> devolverRubrosCategoria(Categoria c) throws Exception{
    	Connection conn = null;
    	List<Rubro> rubros = new ArrayList<Rubro>();
    	PreparedStatement stmt = null;
		try {
			conn = (Connection) abrirConexion();
			String sel ="SELECT idRubro , descRubro as descripcion from rubro WHERE rubHabilitado = 1 AND idCategoria=?";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		stmt.setInt(1, c.getIdCategoria());
    		ResultSet result = stmt.executeQuery();
    		Rubro r = null;
    		while(result.next()){
    			Integer id = result.getInt("idRubro");
    			String descripcion = result.getString("descripcion");
    			r = new Rubro(id, descripcion, c, true);
    			rubros.add(r);
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    	return rubros;
    }  
}
